﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using QLTV_V2.DataAccess;

namespace QLTV_V2
{
    public partial class FrmThongKeSach : DevComponents.DotNetBar.Office2007RibbonForm
    {
        public FrmThongKeSach()
        {
            InitializeComponent();
        }
        ConnectData conn = new ConnectData();
        Utilities uti = new Utilities();
        DataTable dtb = new DataTable();
        string sql="";
        private void FrmThongKeSach_Load(object sender, EventArgs e)
        {
            //Load combobox Loai sach
            DataTable tbLoaiSach = uti.getDataSource("LoaiSach", "TenLoaiSach");
            comb_MaLoaiSach.DataSource = tbLoaiSach;
            DataRow drLoaiSach = tbLoaiSach.NewRow();
            drLoaiSach["MaLoaiSach"] = "all";
            drLoaiSach["TenLoaiSach"] = "Tất cả";
            tbLoaiSach.Rows.InsertAt(drLoaiSach, 0);
            comb_MaLoaiSach.DisplayMember = "TenLoaiSach";
            comb_MaLoaiSach.ValueMember = "MaLoaiSach";
            comb_MaLoaiSach.SelectedIndex = 0;
            //Load combobox tinh trang
            comb_TinhTrang.Items.Add("Tất cả");
            comb_TinhTrang.Items.Add("Tốt");
            comb_TinhTrang.Items.Add("Hỏng");
            comb_TinhTrang.SelectedIndex = 0;
            //Load combobox trnang thai
            comb_TrangThai.Items.Add("Tất cả");
            comb_TrangThai.Items.Add("Sẵn sàng");
            comb_TrangThai.Items.Add("Cho mượn");
            comb_TrangThai.Items.Add("Thanh lý");
            comb_TrangThai.SelectedIndex = 0;
            //combobox MT
            comb_MT.Items.Add("Tất cả");
            comb_MT.Items.Add("Chưa trả");
            comb_MT.Items.Add("Đã trả");
            comb_MT.Items.Add("Đã cho mượn");
            comb_MT.Items.Add("Chưa cho mượn");
            comb_MT.SelectedIndex = 0;
            //date time
            date_Den.Enabled = false;
            date_Tu.Enabled = false;
            date_Den.Value = System.DateTime.Now;
            date_Tu.Value = System.DateTime.Now;
            //
        }

        private void btn_In_Click(object sender, EventArgs e)
        {
            if (txt_TenBC.Text != "")
            {
                frmReportPhieuKK frm = new frmReportPhieuKK();
                frm.dtb = conn.GetDataTable(getSql(txt_TenBC.Text.ToUpper()));
                frm.dk = 4;
                frm.ShowDialog();
            }
            else
                MessageBox.Show("Chưa nhập tiêu đề báo cáo", "Thông báo");
        }
        string getSql(string tenCB)
        {
            if (comb_MT.SelectedIndex == 0 || comb_MT.SelectedIndex==4)
                sql = "select a.MaBanSach,TenDauSach,TenLoaiSach,TenTacGia,TenNXB,NamXB,NgayCN, TinhTrang, TrangThai, N'"+tenCB+"' TenBC " +
                    "from BanSach a, DauSach b, LoaiSach c, TacGia d, NhaXuatBan e " +
                    "where a.MaDauSach=b.MaDauSach and b.MaLoaiSach=c.MaLoaiSach and b.MaTacGia=d.MaTacGia and b.MaNXB=e.MaNXB ";
            else
                sql = "select a.MaBanSach,TenDauSach,TenLoaiSach,TenTacGia,TenNXB,NamXB,NgayCN, TinhTrang, TrangThai,N'" + tenCB + "' TenBC " +
                    "from BanSach a, DauSach b, LoaiSach c, TacGia d, NhaXuatBan e, CTMuonTra f "+
                    "where a.MaDauSach=b.MaDauSach and b.MaLoaiSach=c.MaLoaiSach and b.MaTacGia=d.MaTacGia and b.MaNXB=e.MaNXB and a.MaBanSach=f.MaBanSach ";
            if (comb_MaLoaiSach.SelectedIndex != 0)
                sql += "and b.MaLoaiSach =N'"+comb_MaLoaiSach.SelectedValue.ToString()+"' ";
            if (comb_TinhTrang.SelectedIndex != 0)
                sql += "and TinhTrang=N'" + comb_TinhTrang.SelectedItem.ToString() + "' ";
            if (comb_TrangThai.SelectedIndex != 0)
                sql += "and TrangThai=N'"+comb_TrangThai.SelectedItem.ToString()+"' ";
            //Ngày tháng
            if (check_Tu.Checked == true && check_Den.Checked == false)
                sql += "and NgayCN>='" + date_Tu.Value.ToString("yyyy-MM-dd") + "' ";
            if (check_Tu.Checked == false && check_Den.Checked == true)
                sql += "and NgayCN <='" + date_Den.Value.ToString("yyyy-MM-dd") + "' ";
            if (check_Tu.Checked == true && check_Den.Checked == true)
                sql += "and NgayCN BETWEEN '" + date_Tu.Value.ToString("yyyy-MM-dd") + "' and '" + date_Den.Value.ToString("yyyy-MM-dd") + "' ";
            //Timf kieems thong tin sach muown
            if (comb_MT.SelectedIndex == 1)//chưa trả
                sql += "and NgayTra is null ";
            if (comb_MT.SelectedIndex == 2)//đã trả
                sql += "and NgayTra is not null ";
            if (comb_MT.SelectedIndex == 3)//Đã cho mượn
                sql += "and a.MaBanSach in (select MaBanSach from CTMuonTra) ";
            if (comb_MT.SelectedIndex == 4)
                sql += "and a.MaBanSach not in (select MaBanSach from CTMuonTra)";
            return sql;
        }

        private void btn_XemKq_Click(object sender, EventArgs e)
        {
            dtb=conn.GetDataTable(getSql(txt_TenBC.Text.ToUpper()));
            dgv_Ketqua.DataSource = dtb;
        }
        private void check_Tu_CheckedChanged(object sender, EventArgs e)
        {
            if (check_Tu.Checked == true)
                date_Tu.Enabled = true;
            else
                date_Tu.Enabled = false;
        }

        private void check_Den_CheckedChanged(object sender, EventArgs e)
        {
            if (check_Den.Checked == true)
                date_Den.Enabled = true;
            else
                date_Den.Enabled = false;
        }

        private void dgv_Ketqua_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            uti.dgv_GetSTT_RowHeader(sender, e, dgv_Ketqua);
        }

        private void btn_Thoat_Click(object sender, EventArgs e)
        {
            this.Close();
        }


    }
}
